CREATE TABLE [dbo].[Account](
	[Account_Id] [int] IDENTITY(1,1) NOT NULL,
	[BusinessId] [int] NOT NULL,
	[PromotionId] [int] NULL,
	[Account_No] [varchar](50) NULL,
	[Account_Balance] [money] NOT NULL,
 CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED 
(
	[Account_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [AK_Account] ON [dbo].[Account] 
(
	[BusinessId] ASC,
	[PromotionId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Account]  WITH CHECK ADD  CONSTRAINT [FK_Account_Business] FOREIGN KEY([BusinessId])
REFERENCES [dbo].[Business] ([BusinessId])
GO
ALTER TABLE [dbo].[Account] CHECK CONSTRAINT [FK_Account_Business]
GO
ALTER TABLE [dbo].[Account]  WITH CHECK ADD  CONSTRAINT [FK_Account_Promotion] FOREIGN KEY([PromotionId])
REFERENCES [dbo].[Promotion] ([PromotionId])
GO
ALTER TABLE [dbo].[Account] CHECK CONSTRAINT [FK_Account_Promotion]
GO

CREATE TABLE [dbo].[AccountTransaction](
	[TR_ID] [int] IDENTITY(1,1) NOT NULL,
	[Account_Id] [int] NOT NULL,
	[Timestamp] [datetime] NOT NULL,
	[Amount] [money] NOT NULL,
	[Description] [varchar](50) NULL,
	[Type] [varchar](20) NOT NULL,
	[Account_Balance] [money] NOT NULL,
	[CreditCardNo] [varchar](20) NULL,
 CONSTRAINT [PK_AccountTransaction] PRIMARY KEY CLUSTERED 
(
	[TR_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AccountTransaction]  WITH CHECK ADD  CONSTRAINT [FK_AccountTransaction_Account] FOREIGN KEY([Account_Id])
REFERENCES [dbo].[Account] ([Account_Id])
GO
ALTER TABLE [dbo].[AccountTransaction] CHECK CONSTRAINT [FK_AccountTransaction_Account]
GO

CREATE TABLE [dbo].[Ledger](
	[TR_ID] [int] IDENTITY(1,1) NOT NULL,
	[Timestamp] [datetime] NOT NULL,
	[Account_Id_From] [int] NULL,
	[Account_Id_To] [int] NULL,
	[Amount] [money] NOT NULL,
	[Description] [varchar](50) NULL,
	[Type] [varchar](20) NOT NULL,
	[AccountFromBalance] [money] NULL,
	[AccountToBalance] [money] NULL,
	[CreditCardNo] [varchar](20) NULL,
 CONSTRAINT [PK_Ledger] PRIMARY KEY CLUSTERED 
(
	[TR_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Ledger]  WITH CHECK ADD  CONSTRAINT [FK_Ledger_Account] FOREIGN KEY([Account_Id_From])
REFERENCES [dbo].[Account] ([Account_Id])
GO

ALTER TABLE [dbo].[Ledger] CHECK CONSTRAINT [FK_Ledger_Account]
GO

ALTER TABLE [dbo].[Ledger]  WITH CHECK ADD  CONSTRAINT [FK_Ledger_Account1] FOREIGN KEY([Account_Id_To])
REFERENCES [dbo].[Account] ([Account_Id])
GO

ALTER TABLE [dbo].[Ledger] CHECK CONSTRAINT [FK_Ledger_Account1]
GO
CREATE VIEW [dbo].[vwPromotionMoney]
AS
select Promotion.PromotionId, Promotion.BusinessId, Promotion.RatePerPush,
	max(Account.Account_Balance) as PromotionRest,
	SUM(case when AccountTransaction.[Type] = 'Fee' then -AccountTransaction.Amount else 0 end) as SummaPush,
	SUM(case when AccountTransaction.[Type] = 'FixedCost' then -AccountTransaction.Amount else 0 end) as SummaFixedCost,
	SUM(case when (AccountTransaction.[Type] = 'Transfer') and (sign(AccountTransaction.Amount) = 1) then AccountTransaction.Amount else 0 end) as SummaCharge
from Promotion (nolock) inner join Account (nolock) on Promotion.PromotionId = Account.PromotionId and Promotion.BusinessId = Account.BusinessId
				left join AccountTransaction (nolock) on Account.Account_Id = AccountTransaction.Account_Id
group by Promotion.PromotionId, Promotion.BusinessId, Promotion.RatePerPush

GO
create PROCEDURE spAccount_Add
-- add account row
-- run from other SP (when business or promotion created)
(
	@BusinessId int,
	@PromotionId int = null
)
AS
BEGIN
	SET NOCOUNT ON
	
	declare @Account_No varchar(50)
	
	if (@PromotionId is null)
		set @Account_No = 'Free'
	else
		select @Account_No = 'Promotion ' + convert(varchar(20), PromotionNumber)
		from Promotion
		where PromotionId = @PromotionId
	
	insert into Account
	(BusinessId, PromotionId, Account_No, Account_Balance)
	values (@BusinessId, @PromotionId, @Account_No, 0)
END
go

ALTER PROCEDURE [dbo].[spAPI_Business_RegisterStep1]
	@login varchar(50)
	,@password varchar(50)
	,@email varchar(50)
	,@businessName varchar(100)
	,@web varchar(100)
	,@businessPhone varchar(50)
	,@cellPhone varchar(50)
	,@tollFreePhone varchar(50)
	,@fax varchar(50)
	,@streetAddress varchar(100)
	,@streetAddress2 varchar(100)
	,@city varchar(50)
	,@state varchar(2)
	,@zip varchar(5)
	,@latitude float = null
	,@longitude float = null
	,@gmt int = 0
AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON
	
	DECLARE @IdResult INT
	SET @IdResult = 1
	declare @BusinessId int
	
	IF ISNULL(@zip, '') <> ''
		IF NOT EXISTS(SELECT 1 FROM ZatifyGEO..ZipCode WHERE ZipCode = @zip)
		BEGIN
			SET @IdResult = -3
			SELECT @IdResult AS IdResult, NULL AS SecurityToken
			RETURN
		END
	
	IF ISNULL(@Email, '') <> ''
		IF EXISTS(SELECT 1 FROM [User] (NOLOCK) WHERE Email = @Email) OR
			EXISTS(SELECT 1 FROM Business (NOLOCK) WHERE Email = @Email)
		BEGIN
			SET @IdResult = -2
			SELECT @IdResult AS IdResult, NULL AS SecurityToken
			RETURN
		END
	
	BEGIN TRANSACTION
	
		INSERT INTO UserBusinessLogins(LoginName, LoginType)
		VALUES(@login, 'B')
		if @@error <> 0 return
			
		INSERT INTO [Business]
			([Login]
			,[Email]
			,[Password]
			,[BusinessName]
			,[Web]
			,[BusinessPhone]
			,[CellPhone]
			,[TollFreePhone]
			,[Fax]
			,[StreetAddress1]
			,[StreetAddress2]
			,[IsActive]
			,[Deleted]
			,[City]
			,[StateCode]
			,[ZipCode]
			,[Latitude]
			,[Longitude]
			,Gmt)
		 VALUES
			(@Login
			,@Email
			,@Password
			,@BusinessName
			,@Web
			,@BusinessPhone
			,@CellPhone
			,@TollFreePhone
			,@Fax
			,@StreetAddress
			,@StreetAddress2
			,1
			,0
			,@City
			,@State
			,@Zip
			,@Latitude
			,@Longitude
			,@gmt)		
		
		set @BusinessId = SCOPE_IDENTITY()
		exec spAccount_Add @BusinessId = @BusinessId
		
		declare @SecurityToken uniqueidentifier = NEWID()
		INSERT INTO UserToken(SecurityToken, LoginName, ExpirationDate)
		VALUES(@SecurityToken, @Login, '99991231')
		
		SELECT @IdResult AS IdResult, @SecurityToken AS SecurityToken

	COMMIT TRANSACTION
END
go

ALTER PROCEDURE [dbo].[spAPI_Business_Register]
	@login varchar(50)
	,@password varchar(50)
	,@email varchar(50)
	,@businessName varchar(100)
	,@web varchar(100)
	,@businessPhone varchar(50)
	,@cellPhone varchar(50)
	,@tollFreePhone varchar(50)
	,@fax varchar(50)
	,@streetAddress varchar(100)
	,@streetAddress2 varchar(100)
	,@city varchar(50)
	,@state varchar(2)
	,@zip varchar(5)
	,@description varchar(MAX)
	,@referencedBy varchar(100)
	,@sunFrom datetime
	,@sunTo datetime
	,@sunClosed bit
	,@monFrom datetime
	,@monTo datetime
	,@monClosed bit
	,@tueFrom datetime
	,@tueTo datetime
	,@tueClosed bit
	,@wedFrom datetime
	,@wedTo datetime
	,@wedClosed bit
	,@thuFrom datetime
	,@thuTo datetime
	,@thuClosed bit
	,@friFrom datetime
	,@friTo datetime
	,@friClosed bit
	,@satFrom datetime
	,@satTo datetime
	,@satClosed bit
	,@categories varchar(max)
	,@newCategory1 varchar(250)
	,@newCategory2 varchar(250)
	,@newCategory3 varchar(250)
	,@latitude float
	,@longitude float
	,@gmt int = 0
	,@creditCardNo varchar(20)
	,@creditCardExpDate datetime
	,@creditCardName varchar(50)
	,@creditCardPin varchar(10)
	,@useBusinessAddress bit
	,@billingStreetAddress varchar(100)
	,@billingStreetAddress2 varchar(100)
	,@billingCity varchar(50)
	,@billingState varchar(2)
	,@billingZip varchar(5)
	
	,@PictureContentType VARCHAR(50) = ''
	,@Picture varbinary(max) = null	
AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON
	
	DECLARE @IdResult INT
	SET @IdResult = 1
	
	IF ISNULL(@referencedBy, '') <> ''
		IF NOT EXISTS(SELECT 1 FROM UserBusinessLogins WHERE LoginName = @referencedBy)
		BEGIN
			SET @IdResult = 0
			SELECT @IdResult AS IdResult
			RETURN
		END

	IF ISNULL(@zip, '') <> ''
		IF NOT EXISTS(SELECT 1 FROM ZatifyGEO..ZipCode WHERE ZipCode = @zip)
		BEGIN
			SET @IdResult = -3
			SELECT @IdResult AS IdResult
			RETURN
		END

	IF ISNULL(@billingZip, '') <> ''
		IF NOT EXISTS(SELECT 1 FROM ZatifyGEO..ZipCode WHERE ZipCode = @billingZip)
		BEGIN
			SET @IdResult = -4
			SELECT @IdResult AS IdResult
			RETURN
		END
		
	IF ISNULL(@Email, '') <> ''
		IF EXISTS(SELECT 1 FROM [User] (NOLOCK) WHERE Email = @Email) OR
			EXISTS(SELECT 1 FROM Business (NOLOCK) WHERE Email = @Email)
		BEGIN
			SET @IdResult = -2
			SELECT @IdResult AS IdResult
			RETURN
		END
	
	BEGIN TRANSACTION
	
		INSERT INTO UserBusinessLogins(LoginName, LoginType)
		VALUES(@login, 'B')
		if @@error <> 0 return
	
		INSERT INTO [Business]
			([Login]
			,[Email]
			,[Password]
			,[BusinessName]
			,[Web]
			,[BusinessPhone]
			,[CellPhone]
			,[TollFreePhone]
			,[Fax]
			,[StreetAddress1]
			,[StreetAddress2]
			,[IsActive]
			,[Deleted]
			,[City]
			,[StateCode]
			,[ZipCode]
			,[Description]
			,[CreditCardNo]
			,[CreditCardExpDate]
			,[CreditCardName]
			,[CreditCardPin]
			,[BillingAddress1]
			,[BillingAddress2]
			,[BillingCity]
			,[BillingStateCode]
			,[BillingZipCode]
			,[BillingUse]
			,[ReferencedBy]
			,[Latitude]
			,[Longitude]
			,Gmt
			,PictureContentType
			,Picture)
		 VALUES
			(@Login
			,@Email
			,@Password
			,@BusinessName
			,@Web
			,@BusinessPhone
			,@CellPhone
			,@TollFreePhone
			,@Fax
			,@StreetAddress
			,@StreetAddress2
			,1
			,0
			,@City
			,@State
			,@Zip
			,@Description
			,@CreditCardNo
			,@CreditCardExpDate
			,@CreditCardName
			,@CreditCardPin
			,@BillingStreetAddress
			,@BillingStreetAddress2
			,@BillingCity
			,@BillingState
			,@BillingZip
			,@useBusinessAddress
			,@ReferencedBy
			,@Latitude
			,@Longitude
			,@gmt
			,@PictureContentType
			,@Picture)
		
		declare @businessId int = SCOPE_IDENTITY()
		exec spAccount_Add @BusinessId = @businessId
		
		INSERT INTO BusinessHours(BusinessId, [WeekDay], FromTime, ToTime, Closed)
		values
			(@BusinessId, 'Sun', @SunFrom, @SunTo, @SunClosed)
			,(@BusinessId, 'Mon', @MonFrom, @MonTo, @MonClosed)
			,(@BusinessId, 'Tue', @TueFrom, @TueTo, @TueClosed)
			,(@BusinessId, 'Wed', @WedFrom, @WedTo, @WedClosed)
			,(@BusinessId, 'Thu', @ThuFrom, @ThuTo, @ThuClosed)
			,(@BusinessId, 'Fri', @FriFrom, @FriTo, @FriClosed)
			,(@BusinessId, 'Sat', @SatFrom, @SatTo, @SatClosed)
    
    
		INSERT INTO BusinessCategory(BusinessId, CategoryId)
		SELECT @BusinessId, CONVERT(INT, Items.Item)
		FROM dbo.fnAPI_String_Split(@Categories, ',') AS Items
		
		IF (@newCategory1 IS NOT NULL)
			IF (@newCategory1 <> '')
				INSERT INTO CategoryRequest
				(BusinessId, CategoryName)
				VALUES (@BusinessId, @newCategory1)
		IF (@newCategory2 IS NOT NULL)
			IF (@newCategory2 <> '')
				INSERT INTO CategoryRequest
				(BusinessId, CategoryName)
				VALUES (@BusinessId, @newCategory2)
		IF (@newCategory3 IS NOT NULL)
			IF (@newCategory3 <> '')
				INSERT INTO CategoryRequest
				(BusinessId, CategoryName)
				VALUES (@BusinessId, @newCategory3)
						
		declare @SecurityToken uniqueidentifier = NEWID()
					
		INSERT INTO UserToken(SecurityToken, LoginName, ExpirationDate)
		VALUES(@SecurityToken, @Login, '99991231')
		
		SELECT @IdResult AS IdResult, @SecurityToken AS SecurityToken

	COMMIT TRANSACTION
END
go

ALTER PROCEDURE [dbo].[spBusiness_Update]
(
	@BusinessId INT = 0,
	
	@Login VARCHAR(50),
	@Email VARCHAR(50),
	@Password VARCHAR(50),
	@BusinessName VARCHAR(100),
	@Web VARCHAR(100),
	@BusinessPhone VARCHAR(50),
	@CellPhone VARCHAR(50),
	@TollFreePhone VARCHAR(50),
	@Fax VARCHAR(50),
	@StreetAddress1 VARCHAR(100),
	@StreetAddress2 VARCHAR(100),
	@City VARCHAR(50),
	@StateCode VARCHAR(2),
	@ZipCode VARCHAR(5),
	@Description VARCHAR(MAX),
	
	@CreditCardNo VARCHAR(20),
	@CreditCardExpDate DATETIME,
	@CreditCardName VARCHAR(50),
	@CreditCardPin VARCHAR(10),
	
	@BillingAddress1 VARCHAR(100),
	@BillingAddress2 VARCHAR(100),
	@BillingCity VARCHAR(50),
	@BillingStateCode VARCHAR(2),
	@BillingZipCode VARCHAR(5),
	@BillingUse BIT,
	
	@SunFrom DATETIME,
	@SunTo DATETIME,
	@SunClosed BIT,
	@MonFrom DATETIME,
	@MonTo DATETIME,
	@MonClosed BIT,
	@TueFrom DATETIME,
	@TueTo DATETIME,
	@TueClosed BIT,
	@WedFrom DATETIME,
	@WedTo DATETIME,
	@WedClosed BIT,
	@ThuFrom DATETIME,
	@ThuTo DATETIME,
	@ThuClosed BIT,
	@FriFrom DATETIME,
	@FriTo DATETIME,
	@FriClosed BIT,
	@SatFrom DATETIME,
	@SatTo DATETIME,
	@SatClosed BIT,
	
	@Categories VARCHAR(MAX),
	
	@Picture varbinary(max) = null,
	@PictureContentType varchar(50) = ''
)
AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON
	
	BEGIN TRANSACTION
		IF (@BusinessId = 0)
		BEGIN
			INSERT INTO UserBusinessLogins
			(LoginName, LoginType)
			VALUES(@Login, 'B')
			
			INSERT INTO Business
			([Login], Email, [Password], BusinessName, Web, BusinessPhone, CellPhone, TollFreePhone, Fax, StreetAddress1, StreetAddress2, 
			IsActive, Deleted, City, StateCode, ZipCode, [Description], 
			CreditCardNo, CreditCardExpDate, CreditCardName, CreditCardPin, 
			BillingAddress1, BillingAddress2, BillingCity, BillingStateCode, BillingZipCode, BillingUse,
			Picture, PictureContentType)
			VALUES (@Login, @Email, @Password, @BusinessName, @Web, @BusinessPhone, @CellPhone, @TollFreePhone, @Fax, @StreetAddress1, @StreetAddress2, 
				1, 0, @City, @StateCode, @ZipCode, @Description, 
				@CreditCardNo, @CreditCardExpDate, @CreditCardName, @CreditCardPin, 
				@BillingAddress1, @BillingAddress2, @BillingCity, @BillingStateCode, @BillingZipCode, @BillingUse,
				@Picture, @PictureContentType)
				
			SET @BusinessId = SCOPE_IDENTITY()
			exec spAccount_Add @BusinessId = @BusinessId
			
			INSERT INTO BusinessHours
			(BusinessId, [WeekDay], FromTime, ToTime, Closed)
			SELECT @BusinessId, 'Sun', @SunFrom, @SunTo, @SunClosed
			UNION
			SELECT @BusinessId, 'Mon', @MonFrom, @MonTo, @MonClosed
			UNION
			SELECT @BusinessId, 'Tue', @TueFrom, @TueTo, @TueClosed
			UNION
			SELECT @BusinessId, 'Wed', @WedFrom, @WedTo, @WedClosed
			UNION
			SELECT @BusinessId, 'Thu', @ThuFrom, @ThuTo, @ThuClosed
			UNION
			SELECT @BusinessId, 'Fri', @FriFrom, @FriTo, @FriClosed
			UNION
			SELECT @BusinessId, 'Sat', @SatFrom, @SatTo, @SatClosed
		END
		ELSE BEGIN
			UPDATE Business
			SET [Login] = @Login, Email = @Email, [Password] = @Password, BusinessName = @BusinessName, Web = @Web, 
				BusinessPhone = @BusinessPhone, CellPhone = @CellPhone, TollFreePhone = @TollFreePhone, Fax = @Fax, 
				StreetAddress1 = @StreetAddress1, StreetAddress2 = @StreetAddress2, 
				City = @City, StateCode = @StateCode, ZipCode = @ZipCode, 
				[Description] = @Description, 
				CreditCardNo = @CreditCardNo, CreditCardExpDate = @CreditCardExpDate, CreditCardName = @CreditCardName, CreditCardPin = @CreditCardPin, 
				BillingAddress1 = @BillingAddress1, BillingAddress2 = @BillingAddress2, BillingCity = @BillingCity, BillingStateCode = @BillingStateCode, BillingZipCode = @BillingZipCode, BillingUse = @BillingUse
				,Picture = @Picture, PictureContentType = @PictureContentType
			WHERE (BusinessId = @BusinessId)
			
			UPDATE BusinessHours
			SET FromTime = vw.FromTime, ToTime = vw.ToTime, Closed = vw.Closed
			FROM (SELECT @BusinessId AS BusinessId, 'Sun' AS WD, @SunFrom AS FromTime, @SunTo AS ToTime, @SunClosed AS Closed
				UNION
				SELECT @BusinessId, 'Mon', @MonFrom, @MonTo, @MonClosed
				UNION
				SELECT @BusinessId, 'Tue', @TueFrom, @TueTo, @TueClosed
				UNION
				SELECT @BusinessId, 'Wed', @WedFrom, @WedTo, @WedClosed
				UNION
				SELECT @BusinessId, 'Thu', @ThuFrom, @ThuTo, @ThuClosed
				UNION
				SELECT @BusinessId, 'Fri', @FriFrom, @FriTo, @FriClosed
				UNION
				SELECT @BusinessId, 'Sat', @SatFrom, @SatTo, @SatClosed) AS vw
			WHERE (BusinessHours.BusinessId = vw.BusinessId)
				AND (BusinessHours.[WeekDay] = vw.WD)
		END	
				
		DELETE 
		FROM BusinessCategory
		WHERE (BusinessId = @BusinessId)
			
		INSERT INTO BusinessCategory
		(BusinessId, CategoryId)
		SELECT @BusinessId, CONVERT(INT, Items.Item)
		FROM dbo.fnAPI_String_Split(@Categories, ',') AS Items
	
	COMMIT TRANSACTION
END
go
create PROCEDURE [dbo].[spAccountTransaction_PromotionPay]
-- add transactions, when pay promo
-- run from other SP (when promo submit or budget added), 
-- transaction opened in parent sp (transaction will wrap all nested procedures within it)
(
	@BusinessId int,
	@PromotionId int,
	@SummaPay money,
	@SummaRest money,
	@CreditCardNo varchar(20)
)
AS
BEGIN
	SET NOCOUNT ON
	
	declare @AccountIdFree int, @AccountIdPromo int
	declare @AccountBalanceFree money, @AccountBalancePromo money
	declare @Account_NoPromo varchar(50)
	declare @Timestamp datetime
		
	select @AccountIdFree = Account_Id, @AccountBalanceFree = Account_Balance
	from Account
	where (BusinessId = @BusinessId)
		and (PromotionId is null)
		
	select @AccountIdPromo = Account_Id, @AccountBalancePromo = Account_Balance, @Account_NoPromo = Account_No
	from Account
	where (BusinessId = @BusinessId)
		and (PromotionId = @PromotionId)
	
	set @Timestamp = GETUTCDATE()
	-- insert credit card charge transaction
	insert into AccountTransaction
	(Account_Id, [Timestamp], Amount, [Description], [Type], Account_Balance, CreditCardNo)
	values (@AccountIdFree, @Timestamp, @SummaPay, 'CC Charge', 'CC', @AccountBalanceFree + @SummaPay, @CreditCardNo)
	
	insert into Ledger
	([Timestamp], Account_Id_From, Account_Id_To, Amount, Description, Type, AccountFromBalance, AccountToBalance, CreditCardNo)
	VALUES (@Timestamp, null, @AccountIdFree, @SummaPay, 'CC Charge', 'CC', null, @AccountBalanceFree + @SummaPay, @CreditCardNo)
	
	-- free account balance not updated, becouse this money will go to promo account at once
	
	-- insert movement money to promotion 
	set @Timestamp = GETUTCDATE()
	-- from free
	insert into AccountTransaction
	(Account_Id, [Timestamp], Amount, [Description], [Type], Account_Balance, CreditCardNo)
	values (@AccountIdFree, @Timestamp, -(@SummaPay + @SummaRest), 'Transfer to ' + @Account_NoPromo, 'Transfer', @AccountBalanceFree - @SummaRest, null)
	
	-- update free account
	update Account
	set Account_Balance = @AccountBalanceFree - @SummaRest
	where Account_Id = @AccountIdFree
	
	set @Timestamp = GETUTCDATE()
	-- to promo
	insert into AccountTransaction
	(Account_Id, [Timestamp], Amount, [Description], [Type], Account_Balance, CreditCardNo)
	values (@AccountIdPromo, @Timestamp, @SummaPay + @SummaRest, 'Transfer to ' + @Account_NoPromo, 'Transfer', @AccountBalancePromo + @SummaPay + @SummaRest, null)
	
	-- update promo account
	update Account
	set Account_Balance = @AccountBalancePromo + @SummaPay + @SummaRest
	where Account_Id = @AccountIdPromo
	
	-- mark in ledger
	insert into Ledger
	([Timestamp], Account_Id_From, Account_Id_To, Amount, [Description], [Type], AccountFromBalance, AccountToBalance, CreditCardNo)
	values (@Timestamp, @AccountIdFree, @AccountIdPromo, @SummaPay + @SummaRest, 'Transfer to ' + @Account_NoPromo, 'Transfer', @AccountBalanceFree - @SummaRest, @AccountBalancePromo + @SummaPay + @SummaRest, null)
END
go
ALTER PROCEDURE [dbo].[spAPI_Promotion_Submit]
(
	@Token VARCHAR(255),
	@PromotionId INT,
	@SummaPay money,
	@SummaRest money
)
AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON

	DECLARE @IdResult INT
	DECLARE @BusinessId INT
	
	DECLARE @CampaignBudget MONEY
	DECLARE @CreditCardNo VARCHAR(20)	
	
	SET @IdResult = 0
	EXEC spAPI_Business_CheckToken @Token = @Token, @Result = @IdResult OUT, @BusinessId = @BusinessId OUT
	
	IF @IdResult = 1
		IF EXISTS(SELECT 1 FROM Promotion (NOLOCK) WHERE BusinessId = @BusinessId AND PromotionId = @PromotionId)
		BEGIN
			if not exists (select 1 from PromotionCategory (nolock) where PromotionId = @PromotionId)
			begin
				set @IdResult = -2
				goto ExitWithResult
			end
			
			SELECT @CampaignBudget = ISNULL(CampaignBudget, 0)
			FROM Promotion (NOLOCK)
			WHERE (PromotionId = @PromotionId)
			
			SELECT @CreditCardNo = CreditCardNo
			FROM Business (NOLOCK)
			WHERE (BusinessId = @BusinessId)
			
			begin transaction
				UPDATE Promotion
				SET
					Ready = 1,
					PromotionNumber = (select isnull(max(PromotionNumber) + 1, 1) from Promotion (nolock) WHERE BusinessId = @BusinessId),
					CheckinCode = CONVERT (VARCHAR(4), (1000+ ABS(CHECKSUM(NewId())) % 8999)) 
				WHERE (PromotionId = @PromotionId)
				
				-- open promotion account
				exec spAccount_Add @BusinessId = @BusinessId, @PromotionId = @PromotionId
				
				IF @CampaignBudget > 0
					exec spAccountTransaction_PromotionPay @BusinessId = @BusinessId, @PromotionId = @PromotionId, @SummaPay = @SummaPay, @SummaRest = @SummaRest, @CreditCardNo = @CreditCardNo
			
				exec spPromotion_StartStop @PromotionId = @PromotionId
			commit transaction
		END	
		ELSE
			SET @IdResult = -1

ExitWithResult:
	SELECT @IdResult AS IdResult
END
go
ALTER PROCEDURE [dbo].[spAPI_Promotion_BudgetAdd]
(
	@Token VARCHAR(255),
	@PromotionId INT,
	
	@SummaPay money,
	@SummaRest money,
	
	@Name VARCHAR(200),
	@PromotionText VARCHAR(MAX),
	@Picture VARBINARY(MAX) = NULL,
	@PictureContentType VARCHAR(50) = NULL
)
AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON

	DECLARE @IdResult INT
	DECLARE @BusinessId INT
	DECLARE @CreditCardNo VARCHAR(20)
	DECLARE @Now DATETIME
	
	SET @Now = GETUTCDATE()
	
	SET @IdResult = 0
	EXEC spAPI_Business_CheckToken @Token = @Token, @Result = @IdResult OUT, @BusinessId = @BusinessId OUT
	
	IF @IdResult = 1
		IF EXISTS(SELECT 1 FROM Promotion (NOLOCK) WHERE BusinessId = @BusinessId AND PromotionId = @PromotionId)
		BEGIN
			SELECT @CreditCardNo = CreditCardNo
			FROM Business (NOLOCK)
			WHERE (BusinessId = @BusinessId)
			
			BEGIN TRANSACTION
				exec spAccountTransaction_PromotionPay @BusinessId = @BusinessId, @PromotionId = @PromotionId, @SummaPay = @SummaPay, @SummaRest = @SummaRest, @CreditCardNo = @CreditCardNo
				
				UPDATE Promotion
				SET Name = @Name, PromotionText = @PromotionText, 
					Picture = @Picture, PictureContentType = @PictureContentType,
					CampaignBudget = CampaignBudget + @SummaPay + @SummaRest
				WHERE (PromotionId = @PromotionId)
							
				UPDATE Promotion
				SET StatusCode = 'ACT'
				WHERE (Promotion.PromotionId = @PromotionId)
					AND (
							-- 2, 4 - not have end date
							(Promotion.BaseCostTimingId IN (2, 4)) OR 
							-- 3 - shedule at
							((Promotion.BaseCostTimingId = 3) AND (Promotion.BaseCostTimingScheduledAt <= @Now)) OR
							-- 5 - shedule date range
							((Promotion.BaseCostTimingId = 5) AND (Promotion.BaseCostTimingStarts <= @Now) AND (Promotion.BaseCostTimingEnds > @Now) )
						)
			COMMIT TRANSACTION
		END	
		ELSE
			SET @IdResult = -1

	SELECT @IdResult AS IdResult
END
go
create PROCEDURE spAccountTransaction_PromotionCost
-- add transactions, when fee promotion cost
-- run from other SP (when promo start), 
-- transaction is opened in parent sp (transaction will wrap all nested procedures within it)
(
	@PromotionId int,
	@FixedCost money
)
AS
BEGIN
	SET NOCOUNT ON
	
	declare @AccountIdPromo int
	declare @AccountBalancePromo money
	declare @Account_NoPromo varchar(50)
	declare @Timestamp datetime
	
	select @AccountIdPromo = Account_Id, @AccountBalancePromo = Account_Balance, @Account_NoPromo = Account_No
	from Account
	where (PromotionId = @PromotionId)
		
	set @Timestamp = GETUTCDATE()
	-- from promo account
	insert into AccountTransaction
	(Account_Id, [Timestamp], Amount, [Description], [Type], Account_Balance, CreditCardNo)
	values (@AccountIdPromo, @Timestamp, -@FixedCost, 'FixedCost for ' + @Account_NoPromo, 'FixedCost', @AccountBalancePromo - @FixedCost, null)
	
	-- update promo account
	update Account
	set Account_Balance = @AccountBalancePromo - @FixedCost
	where Account_Id = @AccountIdPromo
	
	-- mark in ledger
	insert into Ledger
	([Timestamp], Account_Id_From, Account_Id_To, Amount, [Description], [Type], AccountFromBalance, AccountToBalance, CreditCardNo)
	values (@Timestamp, @AccountIdPromo, null, @FixedCost, 'FixedCost for ' + @Account_NoPromo, 'FixedCost', @AccountBalancePromo - @FixedCost, null, null)
END
go
alter PROCEDURE spAccountTransaction_PromotionPush
-- add transactions, when fee promotion push
-- run from other SP (when promo push), 
-- transaction is opened in parent sp (transaction will wrap all nested procedures within it)
(
	@PromotionId int,
	@RatePerPush money
)
AS
BEGIN
	SET NOCOUNT ON
	
	declare @AccountIdPromo int
	declare @AccountBalancePromo money
	declare @Account_NoPromo varchar(50)
	declare @Timestamp datetime
	declare @TimestampDate datetime
	
	select @AccountIdPromo = Account_Id, @AccountBalancePromo = Account_Balance, @Account_NoPromo = Account_No
	from Account
	where (PromotionId = @PromotionId)
		
	set @Timestamp = GETUTCDATE()
	set @TimestampDate = DATEADD(DD, (DATEDIFF(DD, 0, @Timestamp)), 0)
	
	-- from promo account
	insert into AccountTransaction
	(Account_Id, [Timestamp], Amount, [Description], [Type], Account_Balance, CreditCardNo)
	values (@AccountIdPromo, @Timestamp, -@RatePerPush, 'Fee for ' + @Account_NoPromo, 'Fee', @AccountBalancePromo - @RatePerPush, null)
	
	-- update promo account
	update Account
	set Account_Balance = @AccountBalancePromo - @RatePerPush
	where Account_Id = @AccountIdPromo
	
	-- mark in ledger
	if exists(select 1 from Ledger where Account_Id_From = @AccountIdPromo and [Timestamp] = @TimestampDate and [Type] = 'Fee')
		update Ledger
		set AccountFromBalance = AccountFromBalance - @RatePerPush, Amount = Amount + @RatePerPush
		where Account_Id_From = @AccountIdPromo 
			and [Timestamp] = @TimestampDate 
			and [Type] = 'Fee'
	else
		insert into Ledger
		([Timestamp], Account_Id_From, Account_Id_To, Amount, [Description], [Type], AccountFromBalance, AccountToBalance, CreditCardNo)
		values (@TimestampDate, @AccountIdPromo, null, @RatePerPush, 'Fee for ' + @Account_NoPromo, 'Fee', @AccountBalancePromo - @RatePerPush, null, null)
END
go
alter PROCEDURE spAccountTransaction_PromotionRest
-- add transactions, when calc promo rest
-- run from other SP (when promo close), 
-- transaction is opened in parent sp (transaction will wrap all nested procedures within it)
(
	@BusinessId int,
	@PromotionId int
)
AS
BEGIN
	SET NOCOUNT ON
	
	declare @AccountIdFree int, @AccountIdPromo int
	declare @AccountBalanceFree money, @AccountBalancePromo money
	declare @Account_NoPromo varchar(50)
	declare @Timestamp datetime
		
	select @AccountIdFree = Account_Id, @AccountBalanceFree = Account_Balance
	from Account
	where (BusinessId = @BusinessId)
		and (PromotionId is null)
		
	select @AccountIdPromo = Account_Id, @AccountBalancePromo = Account_Balance, @Account_NoPromo = Account_No
	from Account
	where (BusinessId = @BusinessId)
		and (PromotionId = @PromotionId)
	
	if @AccountBalancePromo <= 0
		return
	
	-- insert movement money from promotion 
	set @Timestamp = GETUTCDATE()
	-- from promo (to zero)
	insert into AccountTransaction
	(Account_Id, [Timestamp], Amount, [Description], [Type], Account_Balance, CreditCardNo)
	values (@AccountIdPromo, @Timestamp, -@AccountBalancePromo, 'Transfer from ' + @Account_NoPromo, 'Transfer', 0, null)
	
	-- update promo account
	update Account
	set Account_Balance = 0
	where Account_Id = @AccountIdPromo
		
	set @Timestamp = GETUTCDATE()
	-- to free
	insert into AccountTransaction
	(Account_Id, [Timestamp], Amount, [Description], [Type], Account_Balance, CreditCardNo)
	values (@AccountIdFree, @Timestamp, @AccountBalancePromo, 'Transfer from ' + @Account_NoPromo, 'Transfer', @AccountBalanceFree + @AccountBalancePromo, null)
	
	-- update free account
	update Account
	set Account_Balance = @AccountBalanceFree + @AccountBalancePromo
	where Account_Id = @AccountIdFree
	
	-- mark in ledger
	insert into Ledger
	([Timestamp], Account_Id_From, Account_Id_To, Amount, [Description], [Type], AccountFromBalance, AccountToBalance, CreditCardNo)
	values (@Timestamp, @AccountIdPromo, @AccountIdFree, @AccountBalancePromo, 'Transfer from ' + @Account_NoPromo, 'Transfer', 0, @AccountBalanceFree + @AccountBalancePromo, null)
END
go
ALTER PROCEDURE [dbo].[spPromotion_StartStop]
(
	@PromotionId INT = NULL
)
AS
BEGIN
	SET NOCOUNT ON
	set xact_abort on
	
	DECLARE @Now DATETIME
	SET @Now = GETUTCDATE()
	
	declare @promotionIdCurr int, @businessIdCurr int, @fixedCostCurr money
	
	CREATE TABLE #tmpPromotion1
	(
		PromotionId INT,
		Gmt INT,
		FixedCost money
	)
	
	CREATE TABLE #tmpPromotion2
	(
		PromotionId INT,
		BusinessId int,
		Gmt INT,
		WillStop bit default(0)
	)
	
	IF @PromotionId IS NOT NULL
		INSERT INTO #tmpPromotion1
		(PromotionId, Gmt, FixedCost)
		SELECT Promotion.PromotionId, Business.Gmt, Promotion.FixedCost
		FROM Promotion (NOLOCK) INNER JOIN Business (NOLOCK) ON Promotion.BusinessId = Business.BusinessId
		WHERE (Promotion.PromotionId = @PromotionId)
			AND (Promotion.StatusCode = 'PEN')
			AND (Promotion.Deleted = 0)
			AND (Promotion.Ready = 1)
			AND (Promotion.BaseCostTimingId IS NOT NULL)
			AND (
				-- 2, 4 - starts now
				(Promotion.BaseCostTimingId IN (2, 4)) OR 
				-- 3 - shedule at
				((Promotion.BaseCostTimingId = 3) AND (Promotion.BaseCostTimingScheduledAt <= @Now)) OR
				-- 5 - shedule date range
				((Promotion.BaseCostTimingId = 5) AND (Promotion.BaseCostTimingStarts <= @Now))
				)
	ELSE
		INSERT INTO #tmpPromotion1
		(PromotionId, Gmt, FixedCost)
		SELECT Promotion.PromotionId, Business.Gmt, Promotion.FixedCost
		FROM Promotion (NOLOCK) INNER JOIN Business (NOLOCK) ON Promotion.BusinessId = Business.BusinessId
		WHERE (Promotion.StatusCode = 'PEN')
			AND (Promotion.Deleted = 0)
			AND (Promotion.Ready = 1)
			AND (Promotion.BaseCostTimingId IS NOT NULL)
			AND (
				-- 2, 4 - starts now
				(Promotion.BaseCostTimingId IN (2, 4)) OR 
				-- 3 - shedule at
				((Promotion.BaseCostTimingId = 3) AND (Promotion.BaseCostTimingScheduledAt <= @Now)) OR
				-- 5 - shedule date range
				((Promotion.BaseCostTimingId = 5) AND (Promotion.BaseCostTimingStarts <= @Now))
				)
	
	begin transaction
		-- Starts promotion
		UPDATE Promotion
		SET StatusCode = 'ACT'
		FROM #tmpPromotion1
		WHERE (Promotion.PromotionId = #tmpPromotion1.PromotionId)

		-- Make pay for starts promotion
		declare cursorStart cursor for 
		select PromotionId, FixedCost
		from #tmpPromotion1
		
		open cursorStart
		fetch from cursorStart into @promotionIdCurr, @fixedCostCurr
		
		while @@FETCH_STATUS = 0
		begin
			exec spAccountTransaction_PromotionCost @PromotionId = @promotionIdCurr, @FixedCost = @fixedCostCurr
			fetch from cursorStart into @promotionIdCurr, @fixedCostCurr
		end
		
		close cursorStart
		deallocate cursorStart
	commit transaction
							
	IF @PromotionId IS NOT NULL
		INSERT INTO #tmpPromotion2
		(PromotionId, BusinessId, Gmt)
		SELECT Promotion.PromotionId, Promotion.BusinessId, Business.Gmt
		FROM Promotion (NOLOCK) INNER JOIN Business (NOLOCK) ON Promotion.BusinessId = Business.BusinessId
		WHERE (PromotionId = @PromotionId)
			AND (Promotion.StatusCode = 'ACT')
			AND (Promotion.Deleted = 0)
			AND (Promotion.Ready = 1)
			AND (Promotion.BaseCostTimingId IS NOT NULL)
	ELSE
		INSERT INTO #tmpPromotion2
		(PromotionId, BusinessId, Gmt)
		SELECT Promotion.PromotionId, Promotion.BusinessId, Business.Gmt
		FROM Promotion (NOLOCK) INNER JOIN Business (NOLOCK) ON Promotion.BusinessId = Business.BusinessId
		WHERE (Promotion.StatusCode = 'ACT')
			AND (Promotion.Deleted = 0)
			AND (Promotion.Ready = 1)
			AND (Promotion.BaseCostTimingId IS NOT NULL)
				
	-- Mark promo for stop
	update #tmpPromotion2
	set WillStop = 1
	from Promotion
	where (Promotion.PromotionId = #tmpPromotion2.PromotionId)
		AND (Promotion.StatusCode = 'ACT')
		AND (Promotion.BaseCostTimingId = 5) 
		AND ((Promotion.BaseCostTimingEnds <= @Now) or (DATEDIFF(DAY, Promotion.BaseCostTimingStarts, GETDATE()) > 7))
			
	update #tmpPromotion2
	set WillStop = 1
	from Promotion
	WHERE (Promotion.PromotionId = #tmpPromotion2.PromotionId)
		AND (Promotion.StatusCode = 'ACT')
		AND (Promotion.BaseCostTimingId  IN (2, 3, 4)) 
		AND (DATEDIFF(DAY, Promotion.BaseCostTimingScheduledAt, GETDATE()) > 7)
		
	begin transaction
		-- Stop promotion
		update Promotion
		set StatusCode = 'COM', DateCompleted = GETDATE()			
		from #tmpPromotion2
		where (Promotion.PromotionId = #tmpPromotion2.PromotionId)
			and (#tmpPromotion2.WillStop = 1) 
			
		-- Calc rest and save rest
		declare cursorStopped cursor for 
		select PromotionId, BusinessId
		from #tmpPromotion2
		where WillStop = 1
		
		open cursorStopped
		fetch from cursorStopped into @promotionIdCurr, @businessIdCurr
		
		while @@FETCH_STATUS = 0
		begin
			exec spAccountTransaction_PromotionRest @BusinessId = @businessIdCurr, @PromotionId = @promotionIdCurr
			fetch from cursorStopped into @promotionIdCurr, @businessIdCurr
		end
		
		close cursorStopped
		deallocate cursorStopped
	commit transaction
	
	DROP TABLE #tmpPromotion1
	DROP TABLE #tmpPromotion2
END
go
ALTER PROCEDURE [dbo].[spAPI_Promotion_Push]
-- Use in other SP!!!
-- Not for call directly
(
	@UserId INT,
	@Latitude FLOAT,
	@Longitude FLOAT,
	@LocalTime DATETIME = NULL,
	
	@Result XML OUT
)
AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON
	
	declare @promotionIdCurr int, @businessIdCurr int, @ratePerPushCurr money
	
	CREATE TABLE #tmpPromotions
	(
		PromotionId INT,
		BusinessId int,
		RatePerPush MONEY,
		Lat FLOAT,
		Lon FLOAT,
		WillStop bit default(0)
	)
		
	BEGIN TRANSACTION
			INSERT INTO #tmpPromotions
			(PromotionId, BusinessId, RatePerPush, Lat, Lon)
			SELECT PromotionPushQueue.PromotionId, Promotion.BusinessId,
				PromotionPushQueue.RatePerPush, PromotionPushQueue.Latitude, PromotionPushQueue.Longitude
			FROM PromotionPushQueue (NOLOCK) left join Promotion (nolock) on PromotionPushQueue.PromotionId = Promotion.PromotionId
											left join Business (nolock) on Promotion.BusinessId = Business.BusinessId
											left join ProximityRadius (nolock) on Promotion.ProximityRadiusId = ProximityRadius.ProximityRadiusId
			WHERE (PromotionPushQueue.UserId = @UserId)
				AND (
						((Promotion.ProximityRadiusId not in (12, 13)) AND (dbo.fnCalculateDistance(@Latitude, @Longitude, Business.Latitude, Business.Longitude) <= CONVERT(FLOAT, ProximityRadius.ToRadiusFeet / 5280)))
											-- over 200 miles
											OR ((Promotion.ProximityRadiusId = 13) AND (dbo.fnCalculateDistance(@Latitude, @Longitude, Business.Latitude, Business.Longitude) > CONVERT(FLOAT, ProximityRadius.FromRadiusFeet / 5280)))
											-- custom range
											OR ((Promotion.ProximityRadiusId = 12) AND (dbo.fnCalculateDistance(@Latitude, @Longitude, Business.Latitude, Business.Longitude) <= CONVERT(FLOAT, Promotion.CustomRange)))
					)
			
			if @@error <> 0 return
				
			SELECT @Result = (SELECT Promotion.PromotionId, Promotion.PromotionText, Business.BusinessId, Business.BusinessName,
								STR(dbo.fnCalculateDistance(@Latitude, @Longitude, Business.Latitude, Business.Longitude), 38, 2) AS Distance,
								vwBusinessRating.RatingText AS Rating,
								vwBusinessRating.CountReviews AS Reviews,
								15 AS PriceRange,
								Business.StreetAddress1 + ' ' + Business.StreetAddress2 AS [Address],
								Business.Latitude, Business.Longitude,
								Promotion.PromotionText, Business.[Description], 
								
								Business.BusinessPhone, Business.CellPhone, 
								Business.TollFreePhone, Business.Web,
								
								(SELECT Category.CategoryId AS Id, Category.CategoryName AS Name
									FROM BusinessCategory (NOLOCK) LEFT JOIN Category (NOLOCK) ON BusinessCategory.CategoryId = Category.CategoryId
									WHERE BusinessCategory.BusinessId = Business.BusinessId
									FOR XML AUTO, TYPE) AS Categories,
								(SELECT [WeekDay] AS Name, 
									dbo.fnAPI_Format_Time(FromTime) AS Start, 
									dbo.fnAPI_Format_Time(ToTime) AS [End], 
									Closed
								FROM BusinessHours AS [Day] (NOLOCK)
								WHERE [Day].BusinessId = Business.BusinessId
								FOR XML AUTO, TYPE) AS BusinessHours
							FROM #tmpPromotions pp (nolock) INNER JOIN Promotion (NOLOCK) ON pp.PromotionId = Promotion.PromotionId
											LEFT JOIN Business (NOLOCK) ON Promotion.BusinessId = Business.BusinessId
											LEFT JOIN vwBusinessRating ON Business.BusinessId = vwBusinessRating.BusinessId
													--WHERE pp.UserId = @UserId
							FOR XML PATH ('Promotion'))
							
			INSERT INTO PromotionPush
			(PromotionId, UserId, DatePush, Latitude, Longitude, RatePerPush)
			SELECT PromotionId, @UserId, GETUTCDATE(), Lat, Lon, RatePerPush
			FROM #tmpPromotions
			--WHERE (NOT EXISTS(SELECT 1 FROM PromotionPush WHERE (PromotionPush.UserId = @UserId) AND (PromotionPush.PromotionId = #tmpPromotions.PromotionId)))
			
			if @@error <> 0 return
			
			DELETE
			FROM PromotionPushQueue
			WHERE (UserId = @UserId)
				AND (PromotionId IN (SELECT PromotionId FROM #tmpPromotions))

			if @@error <> 0 return
			
			-- account movements
			declare cursorPushed cursor for 
			select PromotionId, RatePerPush
			from #tmpPromotions
						
			open cursorPushed
			fetch from cursorPushed into @promotionIdCurr, @ratePerPushCurr
			
			while @@FETCH_STATUS = 0
			begin
				exec spAccountTransaction_PromotionPush @PromotionId = @promotionIdCurr, @RatePerPush = @ratePerPushCurr
				fetch from cursorPushed into @promotionIdCurr, @ratePerPushCurr
			end
			
			close cursorPushed
			deallocate cursorPushed
			
			update #tmpPromotions
			set WillStop = 1
			from vwPromotionMoney
			where vwPromotionMoney.PromotionId = #tmpPromotions.PromotionId
				and vwPromotionMoney.PromotionRest < vwPromotionMoney.RatePerPush
				
			update Promotion
			set StatusCode = 'COM', DateCompleted = GETDATE()
			from #tmpPromotions				
			where (Promotion.PromotionId = #tmpPromotions.PromotionId)
				and (#tmpPromotions.WillStop = 1)
				
				
			-- Calc rest and save rest
			declare cursorStopped cursor for 
			select PromotionId, BusinessId
			from #tmpPromotions
			where WillStop = 1
			
			open cursorStopped
			fetch from cursorStopped into @promotionIdCurr, @businessIdCurr
			
			while @@FETCH_STATUS = 0
			begin
				exec spAccountTransaction_PromotionRest @BusinessId = @businessIdCurr, @PromotionId = @promotionIdCurr
				fetch from cursorStopped into @promotionIdCurr, @businessIdCurr
			end
			
			close cursorStopped
			deallocate cursorStopped
			
			if @@error <> 0 return
			
	COMMIT TRANSACTION
		
	DROP TABLE #tmpPromotions
END
go
ALTER PROCEDURE [dbo].[spPromotion_GetById]
(
	@PromotionId INT
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	SELECT Promotion.[PromotionId], Promotion.[BusinessId], Promotion.[ProximityRadiusId],
			pr.FromRadius as ProximityFromRadius, pr.ToRadius as ProximityToRadius,
			Promotion.[CustomRange], Promotion.[Over200MilesFlag], 
			Promotion.[RegisteredOutsideOfZip], Promotion.[RegisteredOutsideOfState], Promotion.[RegisteredOutsideOfCity],
			Promotion.[BaseCostTimingId], bct.Timing as BaseCostTimingName,
			Promotion.[BaseCostTimingScheduledAt], Promotion.[BaseCostTimingStarts], Promotion.[BaseCostTimingEnds],
			Promotion.[CampaignBudget], Promotion.[Name],
			Promotion.[PromotionText], Promotion.[PromixityRadiusFixedCost], Promotion.[PromixityRadiusRatePerPush],									
			Promotion.[CostTimingFixedCost], Promotion.[CostTimingRatePerPush],
			Promotion.BaseCostTimingRatePerPush, Promotion.BaseCostTimingFixedCost,
			Promotion.[StatusCode], Promotion.[FixedCost], Promotion.[RatePerPush],
			Promotion.[CheckinCode], Promotion.[Deleted], Promotion.[Ready],									
			
			ISNULL(vwCheckin.CheckIns, 0) AS CheckIns,
						
			vwBusinessRating.RatingText AS Rating,
			vwBusinessRating.CountReviews AS Reviews,
																		
			CASE WHEN (Promotion.RatePerPush = 0) THEN 0
			ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
			END AS MaxPushes,
									
			--ISNULL(vwPushes.Amount, 0) + ISNULL(vwPromotionCost.Amount, 0) AS SpentMoney, 
			isnull(vwPromotionMoney.SummaPush, 0) + ISNULL(vwPromotionMoney.SummaFixedCost, 0) AS SpentMoney,
			ISNULL(vwPushes.CountPushes, 0) AS SpentPushes,
			--ISNULL(vwPay.Amount, 0) - ISNULL(vwPushes.Amount, 0) - ISNULL(vwPromotionCost.Amount, 0) AS UnusedMoney,
			ISNULL(vwPromotionMoney.PromotionRest, 0) as UnusedMoney,
									
			-- Max pushes - Spent Pushes
			CASE WHEN (Promotion.RatePerPush = 0) THEN 0
			ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
			END - ISNULL(vwPushes.CountPushes, 0) AS UnusedPushes,
									
			case when Promotion.[BaseCostTimingId] = 5 then abs(datediff(minute, getutcdate(), Promotion.[BaseCostTimingEnds]))
			else -1 
			end as MinutesLeft,
			
			Promotion.Picture,
			Promotion.PictureContentType
										
	FROM Promotion LEFT JOIN BaseCostTiming bct on bct.BaseCostTimingId = Promotion.BaseCostTimingId
					LEFT JOIN ProximityRadius pr on pr.ProximityRadiusId = Promotion.ProximityRadiusId
					LEFT JOIN vwBusinessRating ON Promotion.BusinessId = vwBusinessRating.BusinessId
					left join vwPromotionMoney on Promotion.PromotionId = vwPromotionMoney.PromotionId,
											
					/*(SELECT COUNT(*) AS CountPushes, SUM(RatePerPush) AS Amount
					FROM PromotionPush
					WHERE PromotionId = @PromotionId) AS vwPushes,
									
					(SELECT SUM(Amount) AS Amount
					FROM PromotionPay
					WHERE PromotionId = @PromotionId) AS vwPay,
									
					(SELECT SUM(FixedCost) AS Amount
					FROM PromotionCost
					WHERE PromotionId = @PromotionId) AS vwPromotionCost,*/
																		
					(SELECT COUNT(*) AS CountPushes
					FROM PromotionPush
					WHERE PromotionId = @PromotionId) AS vwPushes,
					
					(SELECT COUNT(*) AS CheckIns 
					FROM PromotionCheckin 
					WHERE PromotionId = @PromotionId) AS vwCheckin
												
	WHERE (Promotion.PromotionId = @PromotionId)
END
go
ALTER PROCEDURE [dbo].[spPromotion_GetList]
(
	@BusinessId INT
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	SELECT Promotion.[PromotionId], Promotion.[BusinessId], Promotion.[ProximityRadiusId],
			pr.FromRadius as ProximityFromRadius, pr.ToRadius as ProximityToRadius,
			Promotion.[CustomRange], Promotion.[Over200MilesFlag], 
			Promotion.[RegisteredOutsideOfZip], Promotion.[RegisteredOutsideOfState], Promotion.[RegisteredOutsideOfCity],
			Promotion.[BaseCostTimingId], bct.Timing as BaseCostTimingName,
			Promotion.[BaseCostTimingScheduledAt], Promotion.[BaseCostTimingStarts], Promotion.[BaseCostTimingEnds],
			Promotion.[CampaignBudget], Promotion.[Name],
			Promotion.[PromotionText], Promotion.[PromixityRadiusFixedCost], Promotion.[PromixityRadiusRatePerPush],									
			Promotion.[CostTimingFixedCost], Promotion.[CostTimingRatePerPush],
			Promotion.BaseCostTimingRatePerPush, Promotion.BaseCostTimingFixedCost,
			Promotion.[StatusCode], Promotion.[FixedCost], Promotion.[RatePerPush],
			Promotion.[CheckinCode], Promotion.[Deleted], Promotion.[Ready],									
			
			ISNULL(vwCheckin.CheckIns, 0) AS CheckIns,
			
			vwBusinessRating.RatingText AS Rating,
			vwBusinessRating.CountReviews AS Reviews,
																		
			CASE WHEN (Promotion.RatePerPush = 0) THEN 0
			ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
			END AS MaxPushes,
									
			--ISNULL(vwPushes.Amount, 0) + ISNULL(vwPromotionCost.Amount, 0) AS SpentMoney, 
			isnull(vwPromotionMoney.SummaPush, 0) + ISNULL(vwPromotionMoney.SummaFixedCost, 0) AS SpentMoney,
			ISNULL(vwPushes.CountPushes, 0) AS SpentPushes,
			--ISNULL(vwPay.Amount, 0) - ISNULL(vwPushes.Amount, 0) - ISNULL(vwPromotionCost.Amount, 0) AS UnusedMoney,
			ISNULL(vwPromotionMoney.PromotionRest, 0) as UnusedMoney,
									
			-- Max pushes - Spent Pushes
			CASE WHEN (Promotion.RatePerPush = 0) THEN 0
			ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
			END - ISNULL(vwPushes.CountPushes, 0) AS UnusedPushes,
									
			case when Promotion.[BaseCostTimingId] = 5 then abs(datediff(minute, getutcdate(), Promotion.[BaseCostTimingEnds]))
			else -1 
			end as MinutesLeft,
			
			Picture = null,
			PictureContentType = null
										
	FROM Promotion LEFT JOIN BaseCostTiming bct on bct.BaseCostTimingId = Promotion.BaseCostTimingId
					LEFT JOIN ProximityRadius pr on pr.ProximityRadiusId = Promotion.ProximityRadiusId
					LEFT JOIN vwBusinessRating ON Promotion.BusinessId = vwBusinessRating.BusinessId
					/*LEFT JOIN (SELECT PromotionId, COUNT(*) AS CountPushes, SUM(RatePerPush) AS Amount
								FROM PromotionPush
								GROUP BY PromotionId) AS vwPushes ON Promotion.PromotionId = vwPushes.PromotionId
					LEFT JOIN (SELECT PromotionId, SUM(Amount) AS Amount
								FROM PromotionPay
								GROUP BY PromotionId) AS vwPay ON Promotion.PromotionId = vwPay.PromotionId
					LEFT JOIN (SELECT PromotionId, SUM(FixedCost) AS Amount
								FROM PromotionCost
								GROUP BY PromotionId) AS vwPromotionCost ON Promotion.PromotionId = vwPromotionCost.PromotionId*/
					left join vwPromotionMoney on Promotion.PromotionId = vwPromotionMoney.PromotionId
					LEFT JOIN (SELECT PromotionId, COUNT(*) AS CountPushes
								FROM PromotionPush
								GROUP BY PromotionId) AS vwPushes ON Promotion.PromotionId = vwPushes.PromotionId			
					LEFT JOIN (SELECT PromotionId, COUNT(*) AS CheckIns 
								FROM PromotionCheckin 
								GROUP BY PromotionId) AS vwCheckin ON Promotion.PromotionId = vwCheckin.PromotionId
	WHERE (Promotion.BusinessId = @BusinessId)
END
go
ALTER PROCEDURE [dbo].[spAPI_Billing_Transaction_History]
(
	@Token VARCHAR(255),
	@PromotionId INT = NULL,
	@returnAsDataset bit = 0,
	@DateOperation datetime = null
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	DECLARE @IdResult INT
	DECLARE @Result XML
	
	DECLARE @BusinessId INT
	DECLARE @Balance MONEY
		
	SET @IdResult = 0
	EXEC spAPI_Business_CheckToken @Token = @Token, @Result = @IdResult OUT, @BusinessId = @BusinessId OUT
	
	IF @IdResult = 1
	BEGIN
		select @Balance = Account.Account_Balance
		from Account (nolock) 
		where Account.BusinessId = @BusinessId
			and Account.PromotionId is null
		set @Balance = ISNULL(@Balance, 0)
		
		if @returnAsDataset = 0
		begin
			SET @Result = (SELECT @Balance AS CurrentAccoutnBalance,
								(select AccountTransaction.TR_ID as TransactionId,
									DATEADD(DD, (DATEDIFF(DD, 0, AccountTransaction.[Timestamp])), 0) as [Date],
									AccountTransaction.[Type], AccountTransaction.[Description], AccountTransaction.Amount, AccountTransaction.Account_Balance as Balance
								from Account (nolock) inner join AccountTransaction (nolock) on Account.Account_Id = AccountTransaction.Account_Id
								where (Account.BusinessId = @BusinessId)
									and (Account.PromotionId is null)
									and ((DATEADD(DD, (DATEDIFF(DD, 0, AccountTransaction.[Timestamp])), 0) = @DateOperation) or (@DateOperation is null))
								order by AccountTransaction.[Timestamp] desc
								FOR XML PATH ('Transaction'), ROOT('Transactions'), TYPE)
							FOR XML PATH ('TransactionHistory'))
		end
		else
		begin
			select AccountTransaction.TR_ID as TransactionId,
				DATEADD(DD, (DATEDIFF(DD, 0, AccountTransaction.[Timestamp])), 0) as [Date], AccountTransaction.[Type], 
				AccountTransaction.[Description], AccountTransaction.Amount, AccountTransaction.Account_Balance as Balance
			from Account (nolock) inner join AccountTransaction (nolock) on Account.Account_Id = AccountTransaction.Account_Id
			where (Account.BusinessId = @BusinessId)
				and (Account.PromotionId is null)
				and ((DATEADD(DD, (DATEDIFF(DD, 0, AccountTransaction.[Timestamp])), 0) = @DateOperation) or (@DateOperation is null))
			order by AccountTransaction.[Timestamp] desc
			
			return
		end
	END

	if @returnAsDataset = 0		
		SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result 
END
go
ALTER PROCEDURE [dbo].[spBusiness_GetBilling]
	@BusinessId INT,
	@DateOperation datetime = null
AS
BEGIN
	SET NOCOUNT ON
	
	select AccountTransaction.TR_ID as TransactionId, DATEADD(DD, (DATEDIFF(DD, 0, AccountTransaction.[Timestamp])), 0) as [Date],
		AccountTransaction.[Type], AccountTransaction.[Description], 
		case when SIGN(AccountTransaction.Amount) = -1 then -AccountTransaction.Amount else 0 end as Debit,
		case when SIGN(AccountTransaction.Amount) = 1 then AccountTransaction.Amount else 0 end as Credit,
		AccountTransaction.Account_Balance as Balance, '' as Comments
	from Account (nolock) inner join AccountTransaction (nolock) on Account.Account_Id = AccountTransaction.Account_Id
	where (Account.BusinessId = @BusinessId)
		and (Account.PromotionId is null)
		and ((DATEADD(DD, (DATEDIFF(DD, 0, AccountTransaction.[Timestamp])), 0) = @DateOperation) or (@DateOperation is null))
	order by AccountTransaction.[Timestamp] desc
END
go
ALTER PROCEDURE [dbo].[spBusiness_GetCreditCardTransaction]
(
	@TransactionId INT
)
AS
BEGIN
	SET NOCOUNT ON
	
	select DATEADD(DD, (DATEDIFF(DD, 0, AccountTransaction.[Timestamp])), 0) as [Date],
		AccountTransaction.[Type], AccountTransaction.Amount
	from Account (nolock) inner join AccountTransaction (nolock) on Account.Account_Id = AccountTransaction.Account_Id
	where AccountTransaction.TR_ID = @TransactionId
END
go
ALTER PROCEDURE [dbo].[spAPI_User_GetPromotionDetails]
(
	@Token VARCHAR(255),
	@PromotionId INT,
	@returnAsDataSet bit = 0
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	DECLARE @IdResult INT
	DECLARE @UserId INT
	
	DECLARE @Result XML
	
	SET @IdResult = 0
	EXEC spAPI_User_CheckToken @Token = @Token, @Result = @IdResult OUT, @UserId = @UserId OUT
	
	IF @IdResult != 1
	begin
		SET @IdResult = -1
		SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
		return
	end
	
	IF not EXISTS(SELECT 1 FROM PromotionPush WHERE UserId = @UserId AND PromotionId = @PromotionId)
	begin
		SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
		return
	end

	if @returnAsDataSet = 0
	begin	
		SELECT @Result = (SELECT 
								Promotion.[PromotionId],
								Promotion.[BusinessId],
								Promotion.[ProximityRadiusId],
								pr.FromRadius as ProximityFromRadius,
								pr.ToRadius as ProximityToRadius,
								Promotion.[CustomRange],
								Promotion.[Over200MilesFlag],
								Promotion.[RegisteredOutsideOfZip],
								Promotion.[RegisteredOutsideOfState],
								Promotion.[RegisteredOutsideOfCity],
								Promotion.[BaseCostTimingId],
								bct.Timing as BaseCostTimingName,
								Promotion.[BaseCostTimingScheduledAt],
								Promotion.[BaseCostTimingStarts],
								Promotion.[BaseCostTimingEnds],
								Promotion.[CampaignBudget],
								Promotion.[Name],
								Promotion.[PromotionText],
								Promotion.[PromixityRadiusFixedCost],
								Promotion.[PromixityRadiusRatePerPush],									
								Promotion.[CostTimingFixedCost],
								Promotion.[CostTimingRatePerPush],
								Promotion.BaseCostTimingRatePerPush,
								Promotion.BaseCostTimingFixedCost,
								Promotion.[StatusCode],
								Promotion.[FixedCost],
								Promotion.[RatePerPush],
								Promotion.[CheckinCode],
								Promotion.[Deleted],
								Promotion.[Ready],									
								ISNULL(vwCheckin.CheckIns, 0) AS CheckIns,
								
								CASE WHEN (Promotion.RatePerPush = 0) THEN 0
								ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
								END AS MaxPushes,
								
								--ISNULL(vwPushes.Amount, 0) + ISNULL(vwPromotionCost.Amount, 0) AS SpentMoney, 
								isnull(vwPromotionMoney.SummaPush, 0) + ISNULL(vwPromotionMoney.SummaFixedCost, 0) AS SpentMoney,
								ISNULL(vwPushes.CountPushes, 0) AS SpentPushes,
								--ISNULL(vwPay.Amount, 0) - ISNULL(vwPushes.Amount, 0) - ISNULL(vwPromotionCost.Amount, 0) AS UnusedMoney,
								ISNULL(vwPromotionMoney.PromotionRest, 0) as UnusedMoney,
																
								-- Max pushes - Spent Pushes
								CASE WHEN (Promotion.RatePerPush = 0) THEN 0
								ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
								END - ISNULL(vwPushes.CountPushes, 0) AS UnusedPushes,
								
								case
									when Promotion.[BaseCostTimingId] = 5 then abs(datediff(minute, getdate(), Promotion.[BaseCostTimingEnds]))
									else -1 end as MinutesLeft,
									
								(SELECT Category.CategoryId AS Id, Category.CategoryName AS Name,
										dbo.fnAPI_Category_Path(Category.CategoryId) AS FullPath
									FROM PromotionCategory LEFT JOIN Category ON PromotionCategory.CategoryId = Category.CategoryId
									WHERE PromotionCategory.PromotionId = Promotion.PromotionId
									FOR XML AUTO, TYPE) AS Categories
							FROM Promotion left join BaseCostTiming bct on bct.BaseCostTimingId = Promotion.BaseCostTimingId
											left join ProximityRadius pr on pr.ProximityRadiusId = Promotion.ProximityRadiusId
											left join vwPromotionMoney on Promotion.PromotionId = vwPromotionMoney.PromotionId,
								/*(SELECT COUNT(*) AS CountPushes, SUM(RatePerPush) AS Amount
								FROM PromotionPush
								WHERE PromotionId = @PromotionId) AS vwPushes,
								
								(SELECT SUM(Amount) AS Amount
								FROM PromotionPay
								WHERE PromotionId = @PromotionId) AS vwPay,
								
								(SELECT SUM(FixedCost) AS Amount
								FROM PromotionCost
								WHERE PromotionId = @PromotionId) AS vwPromotionCost,*/
								
								
								(SELECT COUNT(*) AS CountPushes
								FROM PromotionPush
								WHERE PromotionId = @PromotionId) AS vwPushes,
								
								(SELECT COUNT(*) AS CheckIns 
								FROM PromotionCheckin 
								WHERE PromotionId = @PromotionId) AS vwCheckin
											
							WHERE (Promotion.PromotionId = @PromotionId)
							FOR XML PATH ('Promotion'))

		SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
	end
	else
	begin
		SELECT 
			Promotion.[PromotionId],
			Promotion.[BusinessId],
			Promotion.[ProximityRadiusId],
			pr.FromRadius as ProximityFromRadius,
			pr.ToRadius as ProximityToRadius,
			Promotion.[CustomRange],
			Promotion.[Over200MilesFlag],
			Promotion.[RegisteredOutsideOfZip],
			Promotion.[RegisteredOutsideOfState],
			Promotion.[RegisteredOutsideOfCity],
			Promotion.[BaseCostTimingId],
			bct.Timing as BaseCostTimingName,
			Promotion.[BaseCostTimingScheduledAt],
			Promotion.[BaseCostTimingStarts],
			Promotion.[BaseCostTimingEnds],
			Promotion.[CampaignBudget],
			Promotion.[Name],
			Promotion.[PromotionText],
			Promotion.[PromixityRadiusFixedCost],
			Promotion.[PromixityRadiusRatePerPush],									
			Promotion.[CostTimingFixedCost],
			Promotion.[CostTimingRatePerPush],
			Promotion.BaseCostTimingRatePerPush,
			Promotion.BaseCostTimingFixedCost,
			Promotion.[StatusCode],
			Promotion.[FixedCost],
			Promotion.[RatePerPush],
			Promotion.[CheckinCode],
			Promotion.[Deleted],
			Promotion.[Ready],									
			ISNULL(vwCheckin.CheckIns, 0) AS CheckIns,
			
			CASE WHEN (Promotion.RatePerPush = 0) THEN 0
			ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
			END AS MaxPushes,
			
			--ISNULL(vwPushes.Amount, 0) + ISNULL(vwPromotionCost.Amount, 0) AS SpentMoney, 
			isnull(vwPromotionMoney.SummaPush, 0) + ISNULL(vwPromotionMoney.SummaFixedCost, 0) AS SpentMoney,
			ISNULL(vwPushes.CountPushes, 0) AS SpentPushes,
			--ISNULL(vwPay.Amount, 0) - ISNULL(vwPushes.Amount, 0) - ISNULL(vwPromotionCost.Amount, 0) AS UnusedMoney,
			ISNULL(vwPromotionMoney.PromotionRest, 0) as UnusedMoney,
			
			-- Max pushes - Spent Pushes
			CASE WHEN (Promotion.RatePerPush = 0) THEN 0
			ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
			END - ISNULL(vwPushes.CountPushes, 0) AS UnusedPushes,
			
			case
				when Promotion.[BaseCostTimingId] = 5 then abs(datediff(minute, getdate(), Promotion.[BaseCostTimingEnds]))
				else -1 end as MinutesLeft
		FROM Promotion left join BaseCostTiming bct on bct.BaseCostTimingId = Promotion.BaseCostTimingId
						left join ProximityRadius pr on pr.ProximityRadiusId = Promotion.ProximityRadiusId
						left join vwPromotionMoney on Promotion.PromotionId = vwPromotionMoney.PromotionId,
								/*(SELECT COUNT(*) AS CountPushes, SUM(RatePerPush) AS Amount
								FROM PromotionPush
								WHERE PromotionId = @PromotionId) AS vwPushes,
								
								(SELECT SUM(Amount) AS Amount
								FROM PromotionPay
								WHERE PromotionId = @PromotionId) AS vwPay,
								
								(SELECT SUM(FixedCost) AS Amount
								FROM PromotionCost
								WHERE PromotionId = @PromotionId) AS vwPromotionCost,*/
								
								
			(SELECT COUNT(*) AS CountPushes
			FROM PromotionPush
			WHERE PromotionId = @PromotionId) AS vwPushes,
			
			(SELECT COUNT(*) AS CheckIns 
			FROM PromotionCheckin 
			WHERE PromotionId = @PromotionId) AS vwCheckin
						
		WHERE (Promotion.PromotionId = @PromotionId)
		
		SELECT
			Category.CategoryId, Category.CategoryName,
			dbo.fnAPI_Category_Path(Category.CategoryId) AS FullPath
		FROM PromotionCategory LEFT JOIN Category ON PromotionCategory.CategoryId = Category.CategoryId
		WHERE PromotionCategory.PromotionId = @PromotionId
	end	
END
go
ALTER PROCEDURE [dbo].[spAPI_Business_GetPromotions]
(
	@Token VARCHAR(255),
	@returnAsDataset bit = 0
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	DECLARE @IdResult INT
	DECLARE @BusinessId INT
	
	DECLARE @Result XML
	
	SET @IdResult = 0
	EXEC spAPI_Business_CheckToken @Token = @Token, @Result = @IdResult OUT, @BusinessId = @BusinessId OUT
	
    DECLARE @Now DATETIME, @BegTime DATETIME, @EndTime DATETIME
    SET @Now = GETUTCDATE()
	SET @BegTime = '00:00:00'
	SET @EndTime = '23:59:59'
	
	IF @IdResult = 1
	begin
		SELECT @Result = (SELECT Promotion.PromotionId, Promotion.BusinessId, 
							Promotion.ProximityRadiusId, Promotion.CustomRange, Promotion.Over200MilesFlag, 
							Promotion.RegisteredOutsideOfZip, Promotion.RegisteredOutsideOfState, Promotion.RegisteredOutsideOfCity, 
							Promotion.BaseCostTimingId, Promotion.BaseCostTimingScheduledAt, Promotion.BaseCostTimingStarts, Promotion.BaseCostTimingEnds, 
							Promotion.CampaignBudget, Promotion.Name, Promotion.PromotionText, 
							Promotion.PromixityRadiusFixedCost, Promotion.PromixityRadiusRatePerPush, 
							Promotion.CostTimingFixedCost, Promotion.CostTimingRatePerPush, 
							Promotion.FixedCost, Promotion.RatePerPush, 
							Promotion.CheckinCode, Promotion.Deleted, Promotion.Ready,
							coalesce(vwPush.PushesCount, 0) as PushesCount,
							case
								when Promotion.BaseCostTimingId <> 5 then Promotion.StatusCode
								when Promotion.BaseCostTimingId = 5 and Promotion.StatusCode = 'COM' then Promotion.StatusCode
								when Promotion.BaseCostTimingId = 5 and Promotion.StatusCode = 'ACT' and
									-- Ongoing, Scheduled timing
									(
										dbo.fnCheckPromoForWithinDay(Promotion.BaseCostTimingId, Promotion.BaseCostTimingStartsWithinDay,
											Promotion.BaseCostTimingEndsWithinDay, Business.Gmt) = 1
									) then Promotion.StatusCode
								else 'PEN' end as StatusCode,
							case
								when Promotion.BaseCostTimingId <> 5 then PromotionStatus.Name
								when Promotion.BaseCostTimingId = 5 and Promotion.StatusCode = 'COM' then PromotionStatus.Name
								when Promotion.BaseCostTimingId = 5 and Promotion.StatusCode = 'ACT' and
									-- Ongoing, Scheduled timing
									(
										dbo.fnCheckPromoForWithinDay(Promotion.BaseCostTimingId, Promotion.BaseCostTimingStartsWithinDay,
											Promotion.BaseCostTimingEndsWithinDay, Business.Gmt) = 1
									) then PromotionStatus.Name
								else 'Pending' end as PromotionStatus,
							
							vwBusinessRating.RatingText AS Rating,
							vwBusinessRating.CountReviews AS Reviews
									
							FROM Promotion LEFT JOIN PromotionStatus ON Promotion.StatusCode = PromotionStatus.StatusCode
											LEFT JOIN (SELECT PromotionId, COUNT(*) AS PushesCount
													FROM PromotionPush
													GROUP BY PromotionId) AS vwPush ON Promotion.PromotionId = vwPush.PromotionId
											LEFT JOIN vwBusinessRating ON Promotion.BusinessId = vwBusinessRating.BusinessId
											LEFT JOIN Business ON Promotion.BusinessId = Business.BusinessId
							WHERE (Promotion.BusinessId = @BusinessId)
								AND (Promotion.Deleted = 0)
								AND (Promotion.Ready = 1)
							FOR XML PATH ('Promotion'), ROOT('Promotions'))
							
		if @returnAsDataset = 1
		begin
			SELECT Promotion.[PromotionId], Promotion.[BusinessId], Promotion.[ProximityRadiusId],
				pr.FromRadius as ProximityFromRadius, pr.ToRadius as ProximityToRadius,
				Promotion.[CustomRange], Promotion.[Over200MilesFlag], 
				Promotion.[RegisteredOutsideOfZip], Promotion.[RegisteredOutsideOfState], Promotion.[RegisteredOutsideOfCity],
				Promotion.[BaseCostTimingId], bct.Timing as BaseCostTimingName,
				Promotion.[BaseCostTimingScheduledAt], Promotion.[BaseCostTimingStarts], Promotion.[BaseCostTimingEnds],
				Promotion.[CampaignBudget], Promotion.[Name],
				Promotion.[PromotionText], Promotion.[PromixityRadiusFixedCost], Promotion.[PromixityRadiusRatePerPush],									
				Promotion.[CostTimingFixedCost], Promotion.[CostTimingRatePerPush],
				Promotion.BaseCostTimingRatePerPush, Promotion.BaseCostTimingFixedCost,
				Promotion.[StatusCode], Promotion.[FixedCost], Promotion.[RatePerPush],
				Promotion.[CheckinCode], Promotion.[Deleted], Promotion.[Ready],									
			
				ISNULL(vwCheckin.CheckIns, 0) AS CheckIns,
								
				vwBusinessRating.RatingText AS Rating,
				vwBusinessRating.CountReviews AS Reviews,
																		
				CASE WHEN (Promotion.RatePerPush = 0) THEN 0
				ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
				END AS MaxPushes,
									
				isnull(vwPromotionMoney.SummaPush, 0) + ISNULL(vwPromotionMoney.SummaFixedCost, 0) AS SpentMoney,
				ISNULL(vwPushes.CountPushes, 0) AS SpentPushes,
				ISNULL(vwPromotionMoney.PromotionRest, 0) as UnusedMoney,
									
				-- Max pushes - Spent Pushes
				CASE WHEN (Promotion.RatePerPush = 0) THEN 0
				ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
				END - ISNULL(vwPushes.CountPushes, 0) AS UnusedPushes,
									
				case when Promotion.[BaseCostTimingId] = 5 then abs(datediff(minute, getutcdate(), Promotion.[BaseCostTimingEnds]))
				else -1 
				end as MinutesLeft,
			
				Picture = null,
				PictureContentType = null
			FROM Promotion LEFT JOIN PromotionStatus ON Promotion.StatusCode = PromotionStatus.StatusCode
							LEFT JOIN BaseCostTiming bct on bct.BaseCostTimingId = Promotion.BaseCostTimingId
							LEFT JOIN ProximityRadius pr on pr.ProximityRadiusId = Promotion.ProximityRadiusId
							LEFT JOIN vwBusinessRating ON Promotion.BusinessId = vwBusinessRating.BusinessId
							LEFT JOIN (SELECT PromotionId, COUNT(*) AS CheckIns 
										FROM PromotionCheckin 
										GROUP BY PromotionId) AS vwCheckin ON Promotion.PromotionId = vwCheckin.PromotionId
							LEFT JOIN (SELECT PromotionId, COUNT(*) AS CountPushes
										FROM PromotionPush
										GROUP BY PromotionId) AS vwPushes ON Promotion.PromotionId = vwPushes.PromotionId
							left join vwPromotionMoney on Promotion.PromotionId = vwPromotionMoney.PromotionId
			WHERE (Promotion.BusinessId = @BusinessId)
				AND (Promotion.Deleted = 0)
				AND (Promotion.Ready = 1)		
				
			RETURN
		END
	END
	
	if @returnAsDataset = 0
		SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
END
go
ALTER PROCEDURE [dbo].[spAPI_Business_GetPromotionDetails]
(
	@Token VARCHAR(255),
	@PromotionId INT,
	@returnAsDataset bit = 0
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	DECLARE @IdResult INT
	DECLARE @BusinessId INT
	
	DECLARE @Result XML
	
	SET @IdResult = 0
	EXEC spAPI_Business_CheckToken @Token = @Token, @Result = @IdResult OUT, @BusinessId = @BusinessId OUT
	
	IF @IdResult = 1
		IF EXISTS(SELECT 1 FROM Promotion WHERE BusinessId = @BusinessId AND PromotionId = @PromotionId)
		BEGIN
			SELECT @Result = (SELECT 
									Promotion.[PromotionId],
									Promotion.[BusinessId],
									Promotion.[ProximityRadiusId],
									pr.FromRadius as ProximityFromRadius,
									pr.ToRadius as ProximityToRadius,
									Promotion.[CustomRange],
									Promotion.[Over200MilesFlag],
									Promotion.[RegisteredOutsideOfZip],
									Promotion.[RegisteredOutsideOfState],
									Promotion.[RegisteredOutsideOfCity],
									Promotion.[BaseCostTimingId],
									bct.Timing as BaseCostTimingName,
									Promotion.[BaseCostTimingScheduledAt],
									Promotion.[BaseCostTimingStarts],
									Promotion.[BaseCostTimingEnds],
									Promotion.[CampaignBudget],
									Promotion.[Name],
									Promotion.[PromotionText],
									Promotion.[PromixityRadiusFixedCost],
									Promotion.[PromixityRadiusRatePerPush],									
									Promotion.[CostTimingFixedCost],
									Promotion.[CostTimingRatePerPush],
									Promotion.BaseCostTimingRatePerPush,
									Promotion.BaseCostTimingFixedCost,
									Promotion.[StatusCode],
									Promotion.[FixedCost],
									Promotion.[RatePerPush],
									Promotion.[CheckinCode],
									Promotion.[Deleted],
									Promotion.[Ready],
									Promotion.BaseCostTimingStartsWithinDay,
									Promotion.BaseCostTimingEndsWithinDay,
									ISNULL(vwCheckin.CheckIns, 0) AS CheckIns,
									
									vwBusinessRating.RatingText AS Rating,
									vwBusinessRating.CountReviews AS Reviews,
																		
									CASE WHEN (Promotion.RatePerPush = 0) THEN 0
									ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
									END AS MaxPushes,
									
									isnull(vwPromotionMoney.SummaPush, 0) + ISNULL(vwPromotionMoney.SummaFixedCost, 0) AS SpentMoney,
									ISNULL(vwPushes.CountPushes, 0) AS SpentPushes,
									ISNULL(vwPromotionMoney.PromotionRest, 0) as UnusedMoney,
									
									-- Max pushes - Spent Pushes
									CASE WHEN (Promotion.RatePerPush = 0) THEN 0
									ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
									END - ISNULL(vwPushes.CountPushes, 0) AS UnusedPushes,
									
									case
										when Promotion.[BaseCostTimingId] = 5 then abs(datediff(minute, getutcdate(), Promotion.[BaseCostTimingEnds]))
										else -1 end as MinutesLeft,
										
									(SELECT Category.CategoryId AS Id, Category.CategoryName AS Name,
											dbo.fnAPI_Category_Path(Category.CategoryId) AS FullPath
										FROM PromotionCategory LEFT JOIN Category ON PromotionCategory.CategoryId = Category.CategoryId
										WHERE PromotionCategory.PromotionId = Promotion.PromotionId
										FOR XML AUTO, TYPE) AS Categories
								FROM Promotion LEFT JOIN BaseCostTiming bct on bct.BaseCostTimingId = Promotion.BaseCostTimingId
											LEFT JOIN ProximityRadius pr on pr.ProximityRadiusId = Promotion.ProximityRadiusId
											LEFT JOIN vwBusinessRating ON Promotion.BusinessId = vwBusinessRating.BusinessId
											left join vwPromotionMoney on Promotion.PromotionId = vwPromotionMoney.PromotionId,
									(SELECT COUNT(*) AS CountPushes
									FROM PromotionPush
									WHERE PromotionId = @PromotionId) AS vwPushes,
																											
									(SELECT COUNT(*) AS CheckIns 
									FROM PromotionCheckin 
									WHERE PromotionId = @PromotionId) AS vwCheckin
												
								WHERE (Promotion.PromotionId = @PromotionId)
								FOR XML PATH ('Promotion'))
								
			if @returnAsDataset = 1
			begin
				SELECT Promotion.[PromotionId], Promotion.[BusinessId], Promotion.[ProximityRadiusId],
						pr.FromRadius as ProximityFromRadius, pr.ToRadius as ProximityToRadius,
						Promotion.[CustomRange], Promotion.[Over200MilesFlag], 
						Promotion.[RegisteredOutsideOfZip], Promotion.[RegisteredOutsideOfState], Promotion.[RegisteredOutsideOfCity],
						Promotion.[BaseCostTimingId], bct.Timing as BaseCostTimingName,
						Promotion.[BaseCostTimingScheduledAt], Promotion.[BaseCostTimingStarts], Promotion.[BaseCostTimingEnds],
						Promotion.[CampaignBudget], Promotion.[Name],
						Promotion.[PromotionText], Promotion.[PromixityRadiusFixedCost], Promotion.[PromixityRadiusRatePerPush],									
						Promotion.[CostTimingFixedCost], Promotion.[CostTimingRatePerPush],
						Promotion.BaseCostTimingRatePerPush, Promotion.BaseCostTimingFixedCost,
						Promotion.[StatusCode], Promotion.[FixedCost], Promotion.[RatePerPush],
						Promotion.[CheckinCode], Promotion.[Deleted], Promotion.[Ready],									
						
						ISNULL(vwCheckin.CheckIns, 0) AS CheckIns,
												
						vwBusinessRating.RatingText AS Rating,
						vwBusinessRating.CountReviews AS Reviews,
																					
						CASE WHEN (Promotion.RatePerPush = 0) THEN 0
						ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
						END AS MaxPushes,
												
						isnull(vwPromotionMoney.SummaPush, 0) + ISNULL(vwPromotionMoney.SummaFixedCost, 0) AS SpentMoney,
						ISNULL(vwPushes.CountPushes, 0) AS SpentPushes,
						ISNULL(vwPromotionMoney.PromotionRest, 0) as UnusedMoney,
												
						-- Max pushes - Spent Pushes
						CASE WHEN (Promotion.RatePerPush = 0) THEN 0
						ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
						END - ISNULL(vwPushes.CountPushes, 0) AS UnusedPushes,
												
						case when Promotion.[BaseCostTimingId] = 5 then abs(datediff(minute, getutcdate(), Promotion.[BaseCostTimingEnds]))
						else -1 
						end as MinutesLeft,
						
						Picture = null,
						PictureContentType = null
				FROM Promotion LEFT JOIN BaseCostTiming bct on bct.BaseCostTimingId = Promotion.BaseCostTimingId
							LEFT JOIN ProximityRadius pr on pr.ProximityRadiusId = Promotion.ProximityRadiusId
							LEFT JOIN vwBusinessRating ON Promotion.BusinessId = vwBusinessRating.BusinessId
							left join vwPromotionMoney on Promotion.PromotionId = vwPromotionMoney.PromotionId,
							
					(SELECT COUNT(*) AS CountPushes
						FROM PromotionPush
						WHERE PromotionId = @PromotionId) AS vwPushes,
														
					(SELECT COUNT(*) AS CheckIns 
						FROM PromotionCheckin 
						WHERE PromotionId = @PromotionId) AS vwCheckin								
				WHERE (Promotion.PromotionId = @PromotionId)
				
				SELECT Category.CategoryId, Category.CategoryName,
						dbo.fnAPI_Category_Path(Category.CategoryId) AS FullPath
				FROM PromotionCategory LEFT JOIN Category ON PromotionCategory.CategoryId = Category.CategoryId
				WHERE PromotionCategory.PromotionId = @PromotionId
				
				return
			end
		END								
		ELSE
			SET @IdResult = -1

	if @returnAsDataset = 0
		SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
END
go
ALTER PROCEDURE [dbo].[spAPI_Billing_Transaction_GetCreditCardTransaction]
(
	@Token VARCHAR(255)
	,@transactionId int
	,@returnAsDataset bit = 0
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	DECLARE @IdResult INT
	DECLARE @Result XML
	
	DECLARE @BusinessId INT
		
	SET @IdResult = 0
	EXEC spAPI_Business_CheckToken @Token = @Token, @Result = @IdResult OUT, @BusinessId = @BusinessId OUT
	
	IF @IdResult = 1
	BEGIN
		if @returnAsDataset = 0
		begin
			SET @Result =
				(select AccountTransaction.TR_ID as TransactionId,
					DATEADD(DD, (DATEDIFF(DD, 0, AccountTransaction.[Timestamp])), 0) as [Date],
					AccountTransaction.[Type], AccountTransaction.Amount
				from Account (nolock) inner join AccountTransaction (nolock) on Account.Account_Id = AccountTransaction.Account_Id
				where AccountTransaction.TR_ID = @TransactionId
				FOR XML PATH ('Transaction'))
		end
		else
		begin
			select AccountTransaction.TR_ID as TransactionId,
					DATEADD(DD, (DATEDIFF(DD, 0, AccountTransaction.[Timestamp])), 0) as [Date],
					AccountTransaction.[Type], AccountTransaction.Amount
			from Account (nolock) inner join AccountTransaction (nolock) on Account.Account_Id = AccountTransaction.Account_Id
			where AccountTransaction.TR_ID = @TransactionId
			
			return
		end
	END
	
	if @returnAsDataset = 0
		SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result 
END
go
ALTER PROCEDURE [dbo].[spAPI_Promotion_GetPayInfo]
(
	@Token VARCHAR(255),
	@PromotionId INT
)
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @IdResult INT
	DECLARE @BusinessId INT
	
	SET @IdResult = 0
	EXEC spAPI_Business_CheckToken @Token = @Token, @Result = @IdResult OUT, @BusinessId = @BusinessId OUT
	
	IF (@IdResult = 1)
		IF (NOT EXISTS(SELECT 1 FROM Promotion (NOLOCK) WHERE BusinessId = @BusinessId AND PromotionId = @PromotionId))
			SET @IdResult = -1

	IF @IdResult = 1
		SELECT @IdResult AS IdResult, 
			Promotion.CampaignBudget, Promotion.RatePerPush, Promotion.FixedCost,
			Business.CreditCardExpDate, Business.CreditCardName, Business.CreditCardNo, Business.CreditCardPin,
			Promotion.Name, 
			isnull(vwPromotionMoney.PromotionRest, 0) as PromoRest
		FROM Promotion (NOLOCK) INNER JOIN Business (NOLOCK) ON Promotion.BusinessId = Business.BusinessId
								left join vwPromotionMoney on Promotion.PromotionId = vwPromotionMoney.PromotionId
		WHERE Promotion.PromotionId = @PromotionId
	ELSE
		SELECT @IdResult AS IdResult
END
go
ALTER PROCEDURE [dbo].[spAPI_Billing_Transaction_GetPromotionTransaction]
(
	@Token VARCHAR(255)
	,@promotionId int
	,@date datetime = null
	,@returnAsDataset bit = 0
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	DECLARE @IdResult INT
	DECLARE @Result XML
	
	DECLARE @BusinessId INT
		
	SET @IdResult = 0
	EXEC spAPI_Business_CheckToken @Token = @Token, @Result = @IdResult OUT, @BusinessId = @BusinessId OUT
	
	IF @IdResult = 1
	BEGIN
		create table #tmpOperations
		(
			IdRow int identity(1, 1),
			DateOperation datetime,
			Amount money, 
			Pushes int
		)
		
		-- 1-st trans is FixedCost
		insert into #tmpOperations
		(DateOperation, Amount, Pushes)
		select DATEADD(DD, (DATEDIFF(DD, 0, AccountTransaction.[Timestamp])), 0), isnull(-AccountTransaction.Amount, 0), 0
		from Account left join AccountTransaction on Account.Account_Id = AccountTransaction.Account_Id
		where Account.PromotionId = @promotionId
			and Account.BusinessId = @BusinessId
			and AccountTransaction.[Type] = 'FixedCost'
		
		insert into #tmpOperations
		(DateOperation, Amount, Pushes)
		select DATEADD(DD, (DATEDIFF(DD, 0, PromotionPush.DatePush)), 0), ISNULL(SUM(PromotionPush.RatePerPush), 0), COUNT(*)
		from Promotion inner join PromotionPush on Promotion.PromotionId = PromotionPush.PromotionId
		where (Promotion.BusinessId = @BusinessId)
			and (Promotion.PromotionId = @PromotionId)
		group by DATEADD(DD, (DATEDIFF(DD, 0, PromotionPush.DatePush)), 0)
		order by DATEADD(DD, (DATEDIFF(DD, 0, PromotionPush.DatePush)), 0)
							
		if (@date is not null)
			delete from #tmpOperations where DateOperation <> @date
			
		if @returnAsDataset = 0
			SET @Result =
				(select DateOperation as [Date], Amount, Pushes
				from #tmpOperations
				order by IdRow
				FOR XML PATH ('Transaction'))
		else
			select DateOperation as [Date], Amount, Pushes
			from #tmpOperations
			order by IdRow
			
		drop table #tmpOperations
	END

	if @returnAsDataset = 0		
		SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result 
END
go
-- DROPS NOT USABLE OBJECTS IF NEEDED
/*
DROP FUNCTION fnPromotionRest_Calc
DROP FUNCTION fnBusiness_PromotionRest_Available
DROP FUNCTION fnTransaction_List
DROP PROCEDURE spPromotionRest_Add
*/